CREATE TABLE [dbo].[UD_WindowFields]
(
[WINDOW_NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_UD_WindowFields_WINDOW_NAME] DEFAULT (''),
[SEQ] [int] NOT NULL CONSTRAINT [DF_UD_WindowFields_SEQ] DEFAULT ((0)),
[TABLE_NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_UD_WindowFields_TABLE_NAME] DEFAULT (''),
[FIELD_NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_UD_WindowFields_FIELD_NAME] DEFAULT (''),
[TABLE_FIELD_NAME] [varchar] (61) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_UD_WindowFields_TABLE_FIELD_NAME] DEFAULT (''),
[VERTICAL_POSITION] [int] NOT NULL CONSTRAINT [DF_UD_WindowFields_VERTICAL_POSITION] DEFAULT ((0)),
[HORIZONTAL_POSITION] [int] NOT NULL CONSTRAINT [DF_UD_WindowFields_HORIZONTAL_POSITION] DEFAULT ((0)),
[PROMPT] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_UD_WindowFields_PROMPT] DEFAULT (''),
[PROMPT_WIDTH] [int] NOT NULL CONSTRAINT [DF_UD_WindowFields_PROMPT_WIDTH] DEFAULT ((0)),
[READ_ONLY] [bit] NOT NULL CONSTRAINT [DF_UD_WindowFields_READ_ONLY] DEFAULT ((0)),
[NEW_ROW] [bit] NOT NULL CONSTRAINT [DF_UD_WindowFields_NEW_ROW] DEFAULT ((0)),
[SHOW_ON_PROFILE] [bit] NOT NULL CONSTRAINT [DF_UD_WindowFields_SHOW_ON_PROFILE] DEFAULT ((0)),
[TIME_STAMP] [timestamp] NULL
) ON [PRIMARY]

GO
CREATE TRIGGER [dbo].[asi_UD_WindowFields_Delete]
    ON [dbo].[UD_WindowFields]
    FOR DELETE
AS
BEGIN
    DELETE Security_Tables
      FROM Security_Tables, deleted
     WHERE UPPER(Security_Tables.TABLE_NAME) = UPPER(REPLACE(REPLACE(deleted.WINDOW_NAME, '-', '_'), ' ', '_')) + '.' + UPPER(REPLACE(REPLACE(deleted.FIELD_NAME, '-', '_'), ' ', '_'))

    DELETE Security_Filters
      FROM Security_Filters, deleted
     WHERE UPPER(Security_Filters.TABLE_NAME) = UPPER(REPLACE(REPLACE(deleted.WINDOW_NAME, '-', '_'), ' ', '_')) + '.' + UPPER(REPLACE(REPLACE(deleted.FIELD_NAME, '-', '_'), ' ', '_'))
END

GO
CREATE TRIGGER [dbo].[asi_UD_WindowFields_Insert]
    ON [dbo].[UD_WindowFields]
    FOR INSERT
AS
BEGIN
    INSERT  Security_Tables(TABLE_NAME,MENU_NAME)
    SELECT     UPPER(REPLACE(REPLACE(WINDOW_NAME, '-', '_'), ' ', '_')) + '.' + UPPER(REPLACE(REPLACE(FIELD_NAME, '-', '_'), ' ', '_')),
            UPPER(WINDOW_NAME) + ' ' + UPPER(FIELD_NAME)
      FROM    inserted

    INSERT  Security_Filters (SECURITY_GROUP, SECURITY_MODE, TABLE_NAME)
    SELECT  sg.SECURITY_GROUP,'BROWSE', UPPER(REPLACE(REPLACE(i.WINDOW_NAME, '-', '_'), ' ', '_')) + '.' + UPPER(REPLACE(REPLACE(i.FIELD_NAME, '-', '_'), ' ', '_'))
      FROM    inserted i,  Security_Groups sg

    INSERT  Security_Filters(SECURITY_GROUP, SECURITY_MODE, TABLE_NAME)
    SELECT    sg.SECURITY_GROUP,'DELETE', UPPER(REPLACE(REPLACE(i.WINDOW_NAME, '-', '_'), ' ', '_')) + '.' + UPPER(REPLACE(REPLACE(i.FIELD_NAME, '-', '_'), ' ', '_'))
      FROM    inserted i,  Security_Groups sg

    INSERT  Security_Filters(SECURITY_GROUP, SECURITY_MODE, TABLE_NAME)
    SELECT    sg.SECURITY_GROUP,'EDIT', UPPER(REPLACE(REPLACE(i.WINDOW_NAME, '-', '_'), ' ', '_')) + '.' + UPPER(REPLACE(REPLACE(i.FIELD_NAME, '-', '_'), ' ', '_'))
      FROM    inserted i,  Security_Groups sg

    INSERT  Security_Filters(SECURITY_GROUP, SECURITY_MODE, TABLE_NAME)
    SELECT    sg.SECURITY_GROUP,'INSERT', UPPER(REPLACE(REPLACE(i.WINDOW_NAME, '-', '_'), ' ', '_')) + '.' + UPPER(REPLACE(REPLACE(i.FIELD_NAME, '-', '_'), ' ', '_'))
      FROM    inserted i,  Security_Groups sg

    INSERT  Security_Filters(SECURITY_GROUP, SECURITY_MODE, TABLE_NAME)
    SELECT    sg.SECURITY_GROUP,'SEARCH', UPPER(REPLACE(REPLACE(i.WINDOW_NAME, '-', '_'), ' ', '_')) + '.' + UPPER(REPLACE(REPLACE(i.FIELD_NAME, '-', '_'), ' ', '_'))
      FROM    inserted i,  Security_Groups sg

    UPDATE     Security_Filters
       SET     TABLE_ACCESSIBLE = 1
      FROM    Security_Filters,inserted
     WHERE     Security_Filters.SECURITY_GROUP = 'Administrator' and
            Security_Filters.TABLE_NAME = UPPER(REPLACE(REPLACE(inserted.WINDOW_NAME, '-', '_'), ' ', '_')) + '.' + UPPER(REPLACE(REPLACE(inserted.FIELD_NAME, '-', '_'), ' ', '_'))
END

GO
ALTER TABLE [dbo].[UD_WindowFields] ADD CONSTRAINT [PK_UD_WindowFields] PRIMARY KEY CLUSTERED ([WINDOW_NAME], [SEQ]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iUD_WindowFieldsTABLE_NAME] ON [dbo].[UD_WindowFields] ([TABLE_NAME], [FIELD_NAME]) ON [PRIMARY]
GO
GRANT REFERENCES ON  [dbo].[UD_WindowFields] TO [IMIS]
GRANT SELECT ON  [dbo].[UD_WindowFields] TO [IMIS]
GRANT INSERT ON  [dbo].[UD_WindowFields] TO [IMIS]
GRANT DELETE ON  [dbo].[UD_WindowFields] TO [IMIS]
GRANT UPDATE ON  [dbo].[UD_WindowFields] TO [IMIS]
GO